﻿<?php
/**
 * @author WEDO2
 * @date created 2009/02/16
 */
require_once(dirname(__FILE__) .'/Config.php');
require_once(dirname(__FILE__) .'/exception/DatabaseException.php');

class Database {
        
    //mysql link connection object
    private $link = null;
    
    //Query string
    private $sql;
    
    //Table and fields list
    private static $tableList;
    
    //the only instance
    private static $instance;
    
    /*
     * construction method
     * Initialize database connection
    */
    private function __construct() {
        $config = Config::getInstance();
        $this->tableList = array();
        $this->link = mysql_connect($config->db_host, $config->db_username, $config->db_password);
        if (!$this->link) {
            throw new DatabaseException('Database connection error: ' . mysql_error());
        } else {
            if (!mysql_select_db($config->db_name))
                throw new DatabaseException('Database name error: ' . mysql_error());
        }
    }
    
    public static function getInstance() {
        if(!self::$instance) {
            self::$instance = new Database();
        }
        return self::$instance;
    }
    
    
    /*
     * Get the table name from module name
     * 
     * 
     */
    public function getTableName($name) {
        if (StringUtils::countUpperCase($name) > 1) {
            $tables = StringUtils::splitByUpperCase($name);
            for($i = 0; $i < count($tables); $i++)
                $tables[$i] = StringUtils::lowerFirstCase($tables[$i]) . DB_PRURAL . '';
            $table_name = implode('_', $tables);
            return DB_TBPRE . $table_name . DB_TBPOS;
        } else {
            return DB_TBPRE . StringUtils::lowerFirstCase($name) . DB_PRURAL . DB_TBPOS;
        }
    }
    
    /*
     * Get the string with table name escaped
     * @param string: the string
     * return atring with table name escaped
     */
    public function escapeTable($str) {
        $config = Config::getInstance();
        return str_replace(array('{', '}'), array($config->db_prefix,''), $str);        
    }
    
    /*
     * Set the query to execute
     * @param sql: the SQL string
     * @param valueList: the array contain the values
     * to be replaced in the query
     * All the table names in the query should be in
     * the form of {tablename}, this will be translated
     * into the correct table name with proper prefix
     */ 
    public function setQuery($sql, $valueList = null) {
        $this->sql = $this->escapeTable(trim($sql));
        if ($valueList) {            
            $valueArray = array_map(mysql_real_escape_string, $valueList);            
            //TODO: Escape back quote
            
            $this->sql = vsprintf($this->sql, $valueArray);
          //  echo $this->sql;
        }
    }
    
    /*
     * Execute the query
     */
    //To do: check the query type (Insert, update...)
    //to return the correct value 
    public function executeQuery() {
        mysql_query("SET NAMES 'UTF8'");
        //echo $this->sql."<hr />";
        $this->result = mysql_query($this->sql);
        
        //LogUtils::getInstance()->log('SQL: ' . $this->sql);
        if (!$this->result)
            throw new DatabaseException('Database querry error: ' . mysql_error());
        return $this->result;
    }
    
    /*
     * load only one result as 
     * an array from the query
     * @param bool: true will return the whole row
     *              false will return the first
     *              element in the row
     */   
    public function loadResult($flag = false) {
        $resource = $this->executeQuery();
        if (!$resource)
            return null;
        $result = null;
        if ($row = mysql_fetch_array($resource))
            $result = $flag ? $row[0] : $row;
        mysql_free_result($resource);
        return $result;
        
    }   
    
    
    /*
     * load all results as 
     * an array from the query
     * @param num: only get the columm num
     */   
    public function loadResultList($num = -1) {
        $resource = $this->executeQuery();
        if (!$resource)
            return null;
        $result = array();
        while ($row = mysql_fetch_array($resource, MYSQL_NUM)) {
            if ($num != -1)
                $result[] = $row[$num];
            else
                $result[] = $row;
        }
        mysql_free_result($resource);
        return $result;
    }
    
    
    /*
     * load only one result as 
     * an array with associated key from the query
     * @param bool: true will return the whole row
     *              false will return the first
     *              element in the row
     */   
    public function loadAssoc($flag = false) {
        $resource = $this->executeQuery();
        if (!$resource)
            return null;
        $result = null;
        if ($row = mysql_fetch_assoc($resource))
            $result = $flag ? $row[0] : $row;
        mysql_free_result($resource);
        return $result;
        
    }   
    
    
    /*
     * load all results as 
     * an associated array array from the query
     * @param key: only get the columm with fieldname is key
     * 
     */   
    public function loadAsscoList($key = null) {
        $resource = $this->executeQuery();
        if (!$resource)
            return null;
        $result = array();
        while ($row = mysql_fetch_assoc($resource)) {
            if (null == $key)
                $result[] = $row;
            else
                $result[$row[$key]] = $row;            
        }
        mysql_free_result($resource);
        return $result;
    }
    
    
    
    /*
     * load only one result as 
     * an object from the query
     */
    public function loadObject() {
        $resource = $this->executeQuery();
        if (!$resource)
            return null;
        $result = null;
        if ($row = mysql_fetch_object($resource))
            $result = $row;
        mysql_free_result($resource);
        return $result;
        
    }

   /*
     * load all results as 
     * an object from the query
     * @param key:  only get the columm with fieldname is key
     */
    public function loadObjectList($class_name = null, $key = null) {
        $resource = $this->executeQuery();
        if (!$resource)
            return null;
        $result = array();
        $tmpResults = $this->loadAsscoList();
        foreach ($tmpResults as $row) {
            $new_class = $class_name ? $class_name : 'stdClass';
            $object = new $new_class;
            foreach ($row as $key => $value)
                $object->$key = $value;
            $result[] = $object;
        }            
        mysql_free_result($resource);
        return $result;
    }
    
    /*
     * Get one row of the fiels names in a table
     * return an array of field names
     */
    public function getOne() {
        $value = $this->loadResult(true);
        return $value;
    }
    
    /*
     * Get one row of the fiels names in a table
     * return an array of field names
     */
    public function getRow() {
        $row = $this->loadAssoc();
        return $row;
    }
    
    /*
     * Get one col of the fields names in a table
     * return an array of col names
     */
    public function getCol() {
        $col = $this->loadResultList(0);
        return $col;
    }
    
    /*
     * Get array all field names in a table
     * return an array of col names
     */
    public function getArray() {
        $arr = $this->loadAsscoList();
        return $arr;
    }
    
    /*
     * Get all the tables in the db
     * return an array of all the table name
     */
    public function getTables() {        
        $this->setQuery('SHOW TABLES');
        return $this->loadResultList();
    }
    
    /*
     * Get all the fiels names in a table     * 
     * return an array of field names
     * @param string: the table to get the fields from
     */
    public function getFields($table_name) {
        if (array_key_exists($table_name, $this->tableList))
            return $this->tableList[$table_name];
        
        $this->setQuery('SHOW FIELDS FROM ' . $table_name);
        //To do: return only Field
        $this->tableList[$table_name] = $this->loadResultList();
        return $this->tableList[$table_name];
    }

    /*
     * Get the id from the last inserted record
     * return int: the id
     */ 
    public function lastInsertId() {
        $this->setQuery('SELECT LAST_INSERT_ID()');
        //return $this->loadResult();
        return $this->getOne();
    }
	
	/*
     * Get array all field names in a table
     * return an array of col names
     */
    public function getAll($tableName, $condition=null) {
		if ($tableName == null) return;
		$sql = 'SELECT * FROM ' . $tableName;
		
		if ($condition != null)
			$sql .= $condition;
		$this->setQuery($sql);
        $arr = $this->loadAsscoList();
        return $arr;
    }


    /*
     * 
     * 
     */
    public function __call($name, $arguments) {
    
        $log = LogUtils::getInstance();
        $startsWithGet = StringUtils::startsWith($name, DB_GET_METHOD);
        $endsWithList = StringUtils::endsWith($name, DB_LIST_SUF);
        $endsWithCount = StringUtils::endsWith($name, DB_COUNT_SUF);
        
        if (!$startsWithGet || (!$endsWithCount && !$endsWithList) ) {
            throw new DatabaseException('Call to undefined method: ' . $name); 
        } else {
            $object = $endsWithCount ? StringUtils::getMidStr($name, DB_GET_METHOD, DB_COUNT_SUF) : StringUtils::getMidStr($name, DB_GET_METHOD, DB_LIST_SUF);
            if ($arguments) {
                $postSql = $arguments[0];                
                $valueList = count($arguments) > 1 ? $arguments[1] : null;
            }
            if ($endsWithCount) {                
                $sql = 'SELECT COUNT(*) FROM ' . $this->getTableName($object) . ' ' . $postSql;                
                $this->setQuery($sql, $valueList);
                return (int)$this->loadResult(true);
            } else {
                $sql = 'SELECT * FROM ' . $this->getTableName($object) . ' ' . $postSql;                
                $this->setQuery($sql, $valueList);
                return $this->loadObjectList($object);
            }
            
        }
     }

	/*
	 * nguyendanghanh@wedo.vn
	 * created: 08/03/2011
	 * updated: 08/03/2011
	 * Insert performance
	 * 		1. Batch INSERT and REPLACE
	 * 		2. Use LOAD DATA instead of INSERT
	 * Use INSERT ... ON DUPLICATE KEY update (INSERT IGNORE) to avoid having to SELECT
	 * http://forge.mysql.com/wiki/Top10SQLPerformanceTips
	 */
	public function insert($table, $mix){
		# if(constant($table)){
			if(is_array($mix)){
				$into= $values= '';
				foreach($mix as $k=> $v){
					@$into.= '`'.mysql_real_escape_string(stripslashes(trim($k))).'`, ';
					@$values.= "'".mysql_real_escape_string(stripslashes(trim($v)))."', ";
				}
				$into= substr($into, 0, -2);
				$values= substr($values, 0, -2);
				$data= array($into, $values);
				$sql= "INSERT INTO ".$table." (%s) VALUES (%s);";
				$sql= vsprintf($sql, $data);
				# print $sql;
				$this->setQuery($sql);
				return $this->executeQuery();
			}
		# }
	}
	/*
	 * nguyendanghanh@wedo.vn
	 * created: 08/03/2011
	 * updated: 08/03/2011
	 * Use SQL_NO_CACHE when you are SELECTing frequently updated data or large sets of data
	 * Use INSERT ... ON DUPLICATE KEY update (INSERT IGNORE) to avoid having to SELECT
	 * http://forge.mysql.com/wiki/Top10SQLPerformanceTips
	 */
	public function update($table, $obj, $mix, $key= 'id', $whr= ''){
		# if(constant($table)){
			if(is_array($mix)){
				$where= "`{$key}`= %d";
				$data= array($obj);
				$set= '';
				foreach($mix as $k=> $v){
					@$set.= '`'.mysql_real_escape_string(stripslashes(trim($k))).'`= ';
					@$set.= "'".mysql_real_escape_string(stripslashes(trim($v)))."', ";
				}
				$set= substr($set, 0, -2);
				if($whr){
					$where.= " %s";
					$data[]= $whr;
				}
				$sql= "UPDATE ".$table." SET ".str_replace('%', '&#37;', $set)." WHERE {$where};";
				$this->setQuery($sql, $data);
				# print vsprintf($sql, $data);die();
				return $this->executeQuery();
			}
		# }
	}
	/*
	 * nguyendanghanh@wedo.vn
	 * created: 08/03/2011
	 * updated: 08/03/2011
	 * Delete small amounts at a time if you can
	 * http://forge.mysql.com/wiki/Top10SQLPerformanceTips
	 */
	public function delete($table, $obj, $key= 'id', $whr= ''){
		# if(constant($table)){
			$where= "`{$key}`= %d";
			$data= array($obj);
			if($whr){
				$where.= " %s";
				$data[]= $whr;
			}
			$sql= "DELETE FROM ".$table." WHERE {$where};";
			# print vsprintf($sql, $data);
			$this->setQuery($sql, $data);
			return $this->executeQuery();
		# }
	}
}

?>
